package services;

import java.awt.Color;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Vector;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class LoadInstructorPack implements Runnable{
	
	private String WorkBookName, PodBookingName;
	private int PackID;
	private FileInputStream fis;
	private FileOutputStream fos;
	private XSSFWorkbook wb;
	private XSSFSheet currentSheet;
	private XSSFRow currentRow;
	private XSSFCell currentCell;
	private Date StartingDate;
	
	public Vector<String> ipHeader;
	public Vector<Vector> allIPClasses;
	public Vector<Vector> nwClasses;
	
	//_PackID 1 = ROWIP, 2 = NAIP
	public LoadInstructorPack(){
		WorkBookName = "";
		PodBookingName = "";
		PackID = 0;
		allIPClasses = new Vector<Vector>();
		nwClasses = new Vector<Vector>();
	}
	
	public void setInput(String _In, int _packID)
	{
		WorkBookName = _In;
		PackID = _packID;		
	}

	public void setDate(Date _startDate)
	{
		StartingDate = _startDate;
	}
	public void setOutput(String _Out)
	{
		PodBookingName = _Out;
	}
	
	public void setDataVector(Vector<Vector> dataVector)
	{
		if (!nwClasses.isEmpty())
		{
			nwClasses.removeAllElements();
		}
		nwClasses = dataVector;
	}
	
	public void loadNWClasses()
	{
		if (WorkBookName.isEmpty())
		{
			return;
		}

		Map<String, Integer> rowIpCol = new HashMap<String, Integer>();
		String currentKey;
		int iTest = 0, iTCell;
		String sheetName = "Checkpoint";
		Vector tmpVector;
		Vector<Vector> tmpVofV;
		Date ReportDate;
		
		try {
			fis = new FileInputStream(WorkBookName);
		    wb = new XSSFWorkbook(fis);
			
			if (currentSheet != null){
				currentSheet = null;}		
			currentSheet = wb.getSheet(sheetName);
			System.out.print("Sheet \"" + sheetName + "\" Activated. \n Loading");			
			if (!allIPClasses.isEmpty()){
				allIPClasses.clear();}
			
			while (iTest < currentSheet.getPhysicalNumberOfRows())
			{
				currentRow = currentSheet.getRow(iTest);
				allIPClasses.add(new Vector<XSSFCell>());
				
				iTCell = 0;
				//In ROW IP checkpoint sheet, only load first 31 columns data.
				while (iTCell < 31)
				{
						currentCell = currentRow.getCell(iTCell);
						allIPClasses.elementAt(iTest).add(currentCell);
						iTCell ++;
				}
				iTest ++;
			}
			System.out.println("\nLoaded " + iTest + " rows data.");
			fis.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		iTest = 0;
		iTCell = 0;
					
		Iterator<Vector> eachItem = allIPClasses.iterator();
		tmpVofV = new Vector<Vector>();
		
		Calendar calTemp = Calendar.getInstance();
		calTemp.setTime(StartingDate);
		calTemp.set(Calendar.HOUR, 0);
		calTemp.set(Calendar.MINUTE,0);
		calTemp.set(Calendar.SECOND, 0);
		StartingDate = calTemp.getTime();
		
		while (eachItem.hasNext())
		{
			tmpVector = eachItem.next();
			try {
					if (tmpVector.size() > 0)
					{
						if ((((XSSFCell) tmpVector.elementAt(0)).getCellType() == Cell.CELL_TYPE_NUMERIC) && 
						(((XSSFCell) tmpVector.elementAt(0)).getCellType() != Cell.CELL_TYPE_BLANK))
						{
							ReportDate = ((XSSFCell) tmpVector.elementAt(0)).getDateCellValue();
							
							if ((StartingDate.before(ReportDate) || StartingDate.compareTo(ReportDate) == 0) && 
									(((XSSFCell) tmpVector.elementAt(1)).getCellType() == Cell.CELL_TYPE_STRING))
							{
								tmpVofV.add(tmpVector);
							}					
						}				
					}
				}
				catch (NullPointerException e)
				{
					
				}
				catch (Throwable e)
				{
					
				}
		}
		ipHeader = new Vector<String>();
		for (iTest=0; iTest<allIPClasses.elementAt(0).size(); iTest++)
		{
			ipHeader.add(allIPClasses.elementAt(0).elementAt(iTest).toString());
		}
		
		for (iTCell=0; iTCell<DataCache.sColumnName.length; iTCell++)
		{
			int tempi = 0;
			String colName = DataCache.sColumnName[iTCell];
			
			if (PackID == 1) //ROWIP
			{
				if (colName == "Instructor")
				{
					colName = "Instructor Name";
				}
			}
			if (PackID == 2) //NAIP
			{
				if (colName == "Student")
				{
					colName = "Part";
				}
				
			}
			Iterator<String> tmpIte = ipHeader.iterator();
			while (tmpIte.hasNext()){
				String current = tmpIte.next();
				if (current.contains(colName))
				{
					rowIpCol.put(DataCache.sColumnName[iTCell], tempi);
				}
				tempi ++ ;
			}
		}		
		
		nwClasses = new Vector<Vector>();
		tmpVector = new Vector<String>();
		for (iTest = 0; iTest<tmpVofV.size(); iTest++)
		{
			tmpVector.removeAllElements();
			if (PackID == 2 && tmpVofV.elementAt(iTest).elementAt(2).toString().contains("Canceled")){
				continue;
			}

			for (iTCell=0; iTCell<DataCache.sColumnName.length; iTCell++)
			{
				currentKey = DataCache.sColumnName[iTCell];
				int index = rowIpCol.get(currentKey);
				if ((tmpVofV.elementAt(iTest).elementAt(index)) != null)
				{
					String tmpSgtr = "";
					
					if (currentKey.equalsIgnoreCase(DataCache.sColumnName[4]))
					{
						Date stDate;
						try {
						stDate =  ((XSSFCell)tmpVofV
										.elementAt(iTest)
										.elementAt(rowIpCol.get(currentKey)))
										.getDateCellValue();
						} catch (java.lang.IllegalStateException e) {
							stDate = DataCache.dToday;
						}
						tmpVector.add(stDate);
						continue;
					}
					tmpSgtr = ((XSSFCell)tmpVofV.
							elementAt(iTest).
							elementAt(rowIpCol.
									get(currentKey))).toString();
					tmpVector.add(tmpSgtr);
				}
				else
				{
					tmpVector.add("");
				}
				
			}
			nwClasses.add(new Vector(tmpVector));
			
		}		
	}

	public void WriteToSheet(){
		String sheetName = "Checklist";
		Map rowIpCol = new HashMap();
		String currentKey;
		DateFormat startdate = new SimpleDateFormat("dd-MMM-yyyy");
		int oviRow = 0, oviCol=0, startRowNum, retryTimes = 3;
		Date tmpDate = null;
		
		XSSFSheet ovSheet;
		XSSFRow colNames;
		
		while (retryTimes > 0)
		{
			try {
				fis = new FileInputStream(PodBookingName);
				wb = new XSSFWorkbook(fis);
				retryTimes = 0;
				
			} catch (FileNotFoundException e1) {
				try {
					FileOutputStream fos = new FileOutputStream(PodBookingName);
					wb = new XSSFWorkbook();
					wb.write(fos);
					fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
				retryTimes --;
				
			} catch (IOException e){
				e.printStackTrace();
			}
		}
		
		System.out.println("Proceding rows ...");
		
		ovSheet = wb.getSheet(sheetName);
		if (ovSheet == null)
		{
			System.out.println("No sheet \"" + sheetName + "\" found, will create a new one.");
			ovSheet = wb.createSheet(sheetName);
			colNames = ovSheet.createRow(0);
			for (oviCol=0; oviCol<DataCache.sColumnName.length; oviCol++)
			{
				XSSFCell zeroCol = colNames.createCell(oviCol);
				zeroCol.setCellValue(DataCache.sColumnName[oviCol]);
			}
		}
		else
		{
			
		}
		
		startRowNum = ovSheet.getLastRowNum() + 1;
		for (oviRow=0 ; oviRow<nwClasses.size(); oviRow++)
		{
			XSSFRow oviRowow = ovSheet.createRow(startRowNum + oviRow);
			XSSFCellStyle ovColor = wb.createCellStyle();
			XSSFColor rowColor = new XSSFColor(Color.lightGray);
			rowColor.setTint(0.7);
			ovColor.setFillForegroundColor(rowColor);
			ovColor.setFillPattern(CellStyle.SOLID_FOREGROUND);
			XSSFCell ovCell = null;
			
			//Starting Date
			XSSFCreationHelper dateFormat = wb.getCreationHelper();
			XSSFCellStyle dateCellStyle = wb.createCellStyle();
			dateCellStyle.setDataFormat(
					dateFormat.createDataFormat().getFormat("m/d"));
			dateCellStyle.setFillForegroundColor(rowColor);
			dateCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			
			//On PodBooking workbook's check_list sheet. Column 3 (C)
			//"Country" 0, "Course" 1, "POD" 2, "Student" 3, "Start Date" 4, "Days" 5, "Image" 6, "Instructor" 7
			try {
				tmpDate = startdate.parse(nwClasses.elementAt(oviRow).elementAt(4).toString());
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			ovCell = oviRowow.createCell(4);			
			ovCell.setCellValue(tmpDate);
			ovCell.setCellStyle(dateCellStyle);
			
			ovCell = oviRowow.createCell(0);
			ovCell.setCellValue(nwClasses.elementAt(oviRow).elementAt(0).toString());
			ovCell.setCellStyle(ovColor);
			
			ovCell = oviRowow.createCell(1);
			ovCell.setCellValue(nwClasses.elementAt(oviRow).elementAt(1).toString());
			ovCell.setCellStyle(ovColor);					

			
			ovCell = oviRowow.createCell(3);
			ovCell.setCellValue(Integer.parseInt(nwClasses.elementAt(oviRow).elementAt(3).toString()));
			ovCell.setCellStyle(ovColor);

			ovCell = oviRowow.createCell(5);
			
			ovCell.setCellValue((int)Double.parseDouble(nwClasses.elementAt(oviRow).elementAt(5).toString()));
			ovCell.setCellStyle(ovColor);
			
			ovCell = oviRowow.createCell(7);
			ovCell.setCellValue(nwClasses.elementAt(oviRow).elementAt(7).toString());
			ovCell.setCellStyle(ovColor);					
			
			ovCell = oviRowow.createCell(2);
			if (nwClasses.elementAt(oviRow).elementAt(2) != null)
			{
				ovCell.setCellValue(nwClasses.elementAt(oviRow).elementAt(2).toString());
			}
			ovCell.setCellStyle(ovColor);
		}
		
		System.out.println();
		System.out.print("\n Saving");
		for (oviCol=0 ; oviCol<nwClasses.elementAt(0).size(); oviCol++)
		{			
			ovSheet.autoSizeColumn(oviCol);
		}
		
		try {
			fos = new FileOutputStream(PodBookingName);
			wb.write(fos);
			System.out.println("done");
			fos.close();  
		} catch (IOException e) {
			e.printStackTrace();
		}
	    
}

	@Override
	public void run() {
		// TODO Auto-generated method stub
		if (PodBookingName.isEmpty())
		{
			loadNWClasses();
			System.out.println(nwClasses);
		}
		else 
		{
			WriteToSheet();
		}
	}
	
}
